USE [wdms]
GO

-- =============================================
-- Author     :		Lohith Ram D V
-- Create date:         07/mar/2011
-- =============================================

create procedure sp_ChangedOwner
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
AS
DECLARE @oldOwner sysname, @newOwner sysname

SELECT
    @oldOwner = 'dbo'
    , @newOwner = 'lohi'

select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
    INFORMATION_SCHEMA.ROUTINES a
where
    a.ROUTINE_TYPE = 'PROCEDURE'
    AND a.SPECIFIC_SCHEMA = @oldOwner
    AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0
	/* SET NOCOUNT ON */
	RETURN
